package com.panopset.db;

import java.beans.PropertyDescriptor;
import java.io.StringWriter;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

/**
 * @param <T>
 *            extends DataObject.
 * @author Karl Dinwiddie POJO class that has getters and setters for each row
 *         field in the table.
 */
public abstract class TableDescription<T extends DataObject> {

    /**
     * @return T class.
     */
    protected abstract Class<T> getDataClass();

    /**
     * @return Schema name.
     */
    protected abstract String getSchemaName();

    /**
     * @return Table name.
     */
    protected abstract String getTableName();

    /**
     * Data manager.
     */
    private final DBmgr dm;

    /**
     * Simple table name.
     */
    private String fullTableName;

    /**
     * Fields.
     */
    private List<DataField> flds;

    /**
     * Unique key fields.
     */
    private List<DataField> ukcs;

    /**
     * @param dataManager
     *            Data manager.
     */
    public TableDescription(final DBmgr dataManager) {
        dm = dataManager;
        if (dm.getTables().isEmpty()) {
            dm.execNoParamSql(getSchemaCreateSql());
        }
        if (!dm.getTables().contains(getFullTableName())) {
            dm.getTables().add(getFullTableName());
            dm.execNoParamSql(getTableCreateSql());
        }
    }

    /**
     * @return All fields.
     */
    private List<DataField> getFlds() {
        if (flds == null) {
            flds = new ArrayList<DataField>();
            flds.add(getTableKeyField());
            populateFields(flds);
        }
        return flds;
    }

    /**
     * @return Unique key combinations.
     */
    private List<DataField> getUkcs() {
        if (ukcs == null) {
            ukcs = new ArrayList<DataField>();
            populateUniqueKeyCombos(ukcs);
        }
        return ukcs;
    }

    /**
     * @return Table name qualified with the schema.
     */
    public final String getFullTableName() {
        if (fullTableName == null) {
            StringWriter sw = new StringWriter();
            sw.append(getSchemaName());
            sw.append(".");
            sw.append(getTableName());
            fullTableName = sw.toString();
        }
        return fullTableName;
    }

    /**
     * Get object by key.
     * @param idKey
     *            Id key.
     * @return Object from row in this table.
     */
    public final T getByKey(final long idKey) {
        return getByIndex(getTableKeyField(), idKey);
    }

    /**
     * Table key field, named &quot;id&quot;.
     */
    private final FieldIdKey tableKeyField = new FieldIdKey("id");

    /**
     * @return &quot;id&quot; field.
     */
    public final FieldIdKey getTableKeyField() {
        return tableKeyField;
    }

    /**
     * Table bind field.
     */
    private FieldIdKey tableBindField;

    /**
     * Use this field to bind this table as a foreign key to another table.
     * @return Table find field.
     */
    public final FieldIdKey getTableBindField() {
        if (tableBindField == null) {
            tableBindField = new FieldIdKey(getTableName());
        }
        return tableBindField;
    }

    /**
     * @param fields
     *            All fields except the id key field for this table.
     */
    protected abstract void populateFields(final List<DataField> fields);

    /**
     * Override this method to define unique key combinations.
     * @param uniqueKeyCominations
     *            Unique key combinations.
     */
    protected void populateUniqueKeyCombos(
            final List<DataField> uniqueKeyCominations) {
    }

    /**
     * @return Create schema SQL.
     */
    private String getSchemaCreateSql() {
        StringWriter sw = new StringWriter();
        sw.append("create schema if not exists ");
        sw.append(getSchemaName());
        return sw.toString();
    }

    /**
     * @return Table create SQL.
     */
    private String getTableCreateSql() {
        StringWriter sw = new StringWriter();
        sw.append("create table if not exists ");
        sw.append(getFullTableName());
        sw.append(" (\n ");
        for (DataField fld : getFlds()) {
            sw.append(fld.getCreateTableCmd());
            sw.append(",\n ");
            if (fld instanceof FieldIdBinder) {
                FieldIdBinder fib = (FieldIdBinder) fld;
                if (!dm.getTables().contains(
                        fib.getBoundTable().getFullTableName())) {
                    dm.execNoParamSql(fib.getBoundTable().getTableCreateSql());
                    dm.getTables().add(fib.getBoundTable().getFullTableName());
                }
            }
        }
        boolean hasComma = true;
        for (DataField fld : getFlds()) {
            if (fld.isPrimaryKey()) {
                if (!hasComma) {
                    sw.append(",\n");
                }
                sw.append(" primary");
                if (fld.isUnique()) {
                    sw.append(" unique");
                }
                sw.append(" key (");
                sw.append(fld.getName());
                sw.append(")");
                hasComma = false;
            }
        }
        for (DataField fld : getFlds()) {
            if (fld.hasAdditionalAttributes()) {
                if (!hasComma) {
                    sw.append(",\n");
                }
                sw.append(fld.getAdditionalAttributes());
                hasComma = false;
            }
        }
        for (DataField fld : getFlds()) {
            if (fld.isIndexed()) {
                if (!hasComma) {
                    sw.append(",\n");
                }
                if (fld.isUnique()) {
                    sw.append(" unique");
                }
                sw.append(" index(");
                sw.append(fld.getName());
                sw.append(")");
                hasComma = false;
            }
        }
        if (!getUkcs().isEmpty()) {
            if (!hasComma) {
                sw.append(",\n");
            }
            sw.append(" unique key (");
            hasComma = true;
            for (DataField uk : getUkcs()) {
                if (!hasComma) {
                    sw.append(",");
                }
                sw.append(uk.getName());
                hasComma = false;
            }
            sw.append(")\n");
            hasComma = false;
        }
        sw.append("\n);\n");
        return sw.toString();
    }

    /**
     * Map to Object.
     * @param fmap
     *            Field map.
     * @return T object.
     */
    public final T map2Object(final Map<String, Object> fmap) {
        if (fmap == null || fmap.isEmpty()) {
            return null;
        }
        StringWriter sw = new StringWriter();
        try {
            T rtn = getDataClass().newInstance();
            sw.append("Mapping ");
            sw.append(fmap.toString());
            sw.append(" to \n");
            sw.append(getDataClass().getCanonicalName());
            sw.append("\n");
            for (DataField fld : getFlds()) {
                if (fld instanceof FieldIdBinder) {
                    FieldIdBinder fldIdBinder = (FieldIdBinder) fld;
                    TableDescription<?> boundTable = fldIdBinder
                            .getBoundTable();
                    String mapKey = fld.getName();
                    Object key = fmap.get(mapKey);
                    DataObject boundDataObject = boundTable.getByIndex(
                            boundTable.getTableKeyField(), key);
                    Method method = new PropertyDescriptor(fld.getName(),
                            getDataClass()).getWriteMethod();
                    sw.append("invoking ");
                    sw.append(method.getName());
                    sw.append("\n on \n");
                    sw.append(rtn.getClass().getCanonicalName());
                    method.invoke(rtn, boundDataObject);
                } else {
                    Object o = fmap.get(fld.getName());
                    if (o != null) {
                        sw.append("Invoking setter for ");
                        sw.append(fld.getName());
                        new PropertyDescriptor(fld.getName(), getDataClass())
                                .getWriteMethod().invoke(rtn, o);
                    }
                }
            }
            return rtn;
        } catch (Exception ex) {
            throw new RuntimeException(sw.toString(), ex);
        }
    }

    /**
     * Object cache.
     */
    private List<T> objs;

    /**
     * @return Object cache.
     */
    public final synchronized List<T> getCache() {
        if (objs == null || objs.size() != getCount()) {
            objs = new ArrayList<T>();
            for (Map<String, Object> map : dm.qry(getSelectAllSql())) {
                objs.add(map2Object(map));
            }
        }
        return objs;
    }

    /**
     * @return select * from getFullTableName.
     */
    public final String getSelectAllSql() {
        StringWriter sw = new StringWriter();
        sw.append("select * from ");
        sw.append(getFullTableName());
        return sw.toString();
    }

    /**
     * Update existing row, this is NOT an upsert.
     * @param idx
     *            Indexes. Unique indexes to be used to find an existing row to
     *            update.
     * @param row
     *            Object to be written.
     * @param dataFields
     *            fields to be updated.
     */
    public final void updateByIndex(final DataField[] idx, final T row,
            final DataField... dataFields) {
        if (idx == null || idx.length < 1) {
            return;
        }
        if (dataFields == null || dataFields.length < 1) {
            return;
        }
        StringWriter sw = new StringWriter();
        sw.append("update ");
        sw.append(getFullTableName());
        sw.append(" set ");
        boolean needsComma = false;
        List<Object> params = new ArrayList<Object>();
        for (DataField fld : dataFields) {
            if (needsComma) {
                sw.append(",");
            }
            sw.append(fld.getName());
            sw.append(" = ?");
            needsComma = true;
            addParameterForUpdate(row, params, fld);
        }
        sw.append(" where ");
        needsComma = false;
        for (DataField fld : idx) {
            if (needsComma) {
                sw.append(" and ");
            }
            sw.append(fld.getName());
            sw.append(" = ?");
            addParameterForUpdate(row, params, fld);
        }
        sw.append("\n");
        SqlSpec sqlSpec = new SqlSpec(sw.toString());
        int i = 0;
        for (Object o : params) {
            i = i + 1;
            sqlSpec.setObject(i, o);
        }
        dm.exec(sqlSpec);
    }

    /**
     * Clear all records in the table.
     */
    public final void clear() {
        dm.exec(new SqlSpec("delete from " + getFullTableName()));
    }

    /**
     * @param row DataObject representing a data row.
     * @param fld Field to get.
     * @return Result of reflected getter for fld.
     */
    private Object getReflection(final DataObject row,

    final DataField fld) {
        try {
            return getReflectedValue(row, fld);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * Get reflected value.
     * @param row
     *            Object to get value from.
     * @param fld
     *            Field in object to get value from.
     * @return Result of reflected read method.
     * @throws Exception
     *             Exception.
     */
    private Object getReflectedValue(final DataObject row, final DataField fld)
            throws Exception {
        Object resObject = null;
        if (fld instanceof FieldIdBinder) {
            FieldIdBinder fib = (FieldIdBinder) fld;
            TableDescription<?> bt = fib.getBoundTable();
            DataObject boundDataObject = (DataObject) new PropertyDescriptor(
                    bt.getTableName(), getDataClass()).getReadMethod().invoke(
                    row);
            long id = boundDataObject.getId();
            if (id == -1) {
                id = bt.getIdByIndexes(boundDataObject);
            }
            if (id == -1) {
                id = bt.insert(boundDataObject);
            }
            resObject = id;
        } else {
            resObject = new PropertyDescriptor(fld.getName(), getDataClass())
                    .getReadMethod().invoke(row);
            if (resObject instanceof Object[]) {
                if (((Object[]) resObject).length == 1) {
                    resObject = ((Object[]) resObject)[0];
                }
            }
        }
        if (fld instanceof FieldVarchar && resObject != null) {
            FieldVarchar fvc = (FieldVarchar) fld;
            String rslt = resObject.toString();
            rslt = rslt.replace("'", "''");
            if (rslt.length() > fvc.getSize()) {
                rslt = rslt.substring(0, fvc.getSize());
            }
            resObject = rslt;
        }
        return resObject;
    }

    /**
     * Add parameter for update.
     * @param row
     *            Object that has key we need.
     * @param params
     *            Params that need the key.
     * @param fld
     *            Key field.
     */
    private void addParameterForUpdate(final DataObject row,
            final List<Object> params, final DataField fld) {
        try {
            Object obj = getReflection(row, fld);
            params.add(obj);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * @param row
     *            DataObject with keys populated.
     * @return -1 if not found, otherwise the id of the found object
     */
    public final long getIdByIndexes(final DataObject row) {
        StringWriter sw = new StringWriter();
        sw.append("select id from ");
        sw.append(getFullTableName());
        sw.append(" where ");
        List<DataField> indexes = new ArrayList<DataField>();
        for (DataField fld : getFlds()) {
            if (fld.isIndexed() && !"id".equals(fld.getName())) {
                indexes.add(fld);
            }
        }
        boolean needsComma = false;
        for (DataField idf : indexes) {
            if (needsComma) {
                sw.append(" and ");
            } else {
                needsComma = true;
            }
            sw.append(idf.getName());
            sw.append(" = ?");
        }
        SqlSpec dc = new SqlSpec(sw.toString());
        int i = 0;
        for (DataField idf : indexes) {
            Object obj = getReflection(row, idf);
            if (obj == null || obj.toString().length() < 1) {
                return -1;
            }
            i = i + 1;
            dc.setObject(i, obj);
        }

        if (dc.getParams().size() < 1
                || dc.getParams().size() != indexes.size()) {
            throw new RuntimeException("\ntable class: "
                    + this.getClass().getName() + "\nforeign key class: "
                    + row.getClass().getName() + "\nforeign key data: "
                    + row.toString() + " " + dc.getParams().size()
                    + " params and " + indexes.size() + " indexes.");
        }

        List<Map<String, Object>> result = dm.qry(dc);
        if (result == null || result.size() < 1) {
            return -1;
        }
        return (Long) result.get(0).get("id");
    }

    /**
     * Insert DataObject in to table row.
     * @param row
     *            Data object.
     * @return Unique key for the DataObject in the table, the ID.
     */
    public final long insert(final DataObject row) {
        StringWriter sw = new StringWriter();
        sw.append("insert into ");
        sw.append(getFullTableName());
        sw.append("\n (");
        boolean needsComma = false;
        for (DataField fld : getFlds()) {
            if (fld instanceof FieldIdKey) {
                continue;
            }
            if (needsComma) {
                sw.append(",");
            }
            needsComma = true;
            sw.append(fld.getName());
        }
        sw.append("\n) values (");
        sw.append("");
        List<Object> params = new ArrayList<Object>();
        needsComma = false;
        for (DataField fld : getFlds()) {
            if (fld instanceof FieldIdKey) {
                continue;
            }
            if (needsComma) {
                sw.append(",");
            }
            sw.append("?");
            needsComma = true;
            addParameterForUpdate(row, params, fld);
        }
        sw.append("\n)");
        SqlSpec dc = new SqlSpec(sw.toString());
        int i = 0;
        for (Object o : params) {
            i = i + 1;
            dc.setObject(i, o);
        }
        return dm.execUpdate(dc, getTableKeyField().getName());
    }

    /**
     * @param dataField
     *            indexed DataField.
     * @param value
     *            index value.
     * @return WARNING, will return null if Object does not exist.
     */
    public final T getByIndex(final DataField dataField, final Object value) {
        SqlSpec sqlSpec = new SqlSpec(getByIndexSql(dataField));
        sqlSpec.setObject(1, value);
        List<Map<String, Object>> result = dm.qry(sqlSpec);
        if (result == null || result.size() < 1) {
            return null;
        }
        return map2Object(result.get(0));
    }

    /**
     * @return Number of rows in the table.
     */
    public final long getCount() {
        SqlSpec sqlSpec = new SqlSpec(getCountSql());
        List<Map<String, Object>> rslt = dm.qry(sqlSpec);
        for (Entry<String, Object> e : rslt.get(0).entrySet()) {
            return Long.parseLong(e.getValue().toString());
        }
        return 0;
    }

    /**
     * @return select count(*) from getFullTableName().
     */
    private String getCountSql() {
        StringWriter sw = new StringWriter();
        sw.append("select count(*) from ");
        sw.append(getFullTableName());
        return sw.toString();
    }

    /**
     * @param dataField
     *            Indexed field.
     * @return Get by dataField index SQL.
     */
    private String getByIndexSql(final DataField dataField) {
        StringWriter sw = new StringWriter();
        sw.append("select * from ");
        sw.append(getFullTableName());
        sw.append(" where ");
        sw.append(dataField.getName());
        sw.append(" = ?");
        return sw.toString();
    }
}
